package com.lee.jdbc;

import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

@SuppressWarnings("Duplicates")
public class Test1 {


    @Test
    public void test1() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "lixin";
        //在mysql连接驱动6及其以后不需要加载驱动，很多jar也是有自动注册驱动的功能，
        //Class.forName("com.mysql.jdbc.Driver");
        //驱动管理器遍历所有注册过的驱动器，找到一个能够使用数据库url中子协议的驱动程序
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) AS COUNT FROM STUDENT");
        while (resultSet.next()) {
            //数据库的列序号从1开始
            int count = resultSet.getInt(1);
//            int count = resultSet.getInt("count");
            System.out.println(count);
        }
        resultSet.close();
        statement.close();
        connection.close();
    }


    /**
     * 使用列明获取也是通过索引获取
     * public int getInt(String columnName) throws SQLException {
     * return getInt(findColumn(columnName));
     * }
     *
     * @throws SQLException
     */
    @Test
    public void test2() {

        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "lixin";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            try {
                connection = DriverManager.getConnection(url, user, password);
                statement = connection.createStatement();
                resultSet = statement.executeQuery("SELECT * FROM STUDENT");
                while (resultSet.next()) {
                    //返回的结果还是无法直接映射成对象。要不然还要mybatis干嘛
                    System.out.println(resultSet.getObject(1));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (resultSet != null) {
                    resultSet.close();
                }
                statement.close();
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    @Test
    public void test3() {

        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "lixin";
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            try {
                connection = DriverManager.getConnection(url, user, password);
                String sql = "SELECT * FROM STUDENT WHERE ID = ?";
                statement = connection.prepareStatement(sql);
                statement.setInt(1, 2);
                resultSet = statement.executeQuery();
                while (resultSet.next()) {
                    //返回的结果还是无法直接映射成对象。要不然还要mybatis干嘛
                    System.out.println(resultSet.getObject(2));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (resultSet != null) {
                    resultSet.close();
                }
                statement.close();
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * get a connection from properties from a file(database.properties)
     */
    @Test
    public void test4() throws SQLException, IOException {
        final Map<String, String> dbProperties = getDBProperties();
        try (Connection connection = DriverManager.getConnection(dbProperties.get("url"), dbProperties.get("user"), dbProperties.get("password"))) {
            String sql = "SELECT * FROM STUDENT WHERE ID = ?";
            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                statement.setInt(1, 2);
                try (ResultSet resultSet = statement.executeQuery()) {
                    while (resultSet.next()) {
                        System.out.println(resultSet.getObject(2));
                    }
                }
            }
        }
    }


    /**
     * 使用Clob或者Blob(未写完)
     */
    @Test
    public void test5() throws SQLException, IOException {

        final Map<String, String> dbProperties = getDBProperties();
        try (Connection connection = DriverManager.getConnection(dbProperties.get("url"), dbProperties.get("user"), dbProperties.get("password"))) {
            String sql = "SELECT * FROM DESC WHERE ID = ?";
            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                Blob blob = connection.createBlob();
                int offset = 0;
                OutputStream out = blob.setBinaryStream(offset);
//                ImageIO.write(ImageIO.read(1), "PNG", out);

                statement.setInt(1, 1);
                try (ResultSet resultSet = statement.executeQuery()) {
                    while (resultSet.next()) {
                        System.out.println(resultSet.getObject(2));
                    }
                }
            }
        }
    }





    /**
     * 获取自动生成的主键
     *
     * @throws SQLException
     */
    @Test
    public void test6() throws SQLException {
        final Map<String, String> dbProperties = getDBProperties();
        try (Connection connection = DriverManager.getConnection(dbProperties.get("url"), dbProperties.get("user"), dbProperties.get("password"))) {
            String sql = "INSERT INTO STUDENT VALUE (null, ?, ?)";
            try (PreparedStatement insertStat = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                insertStat.setString(1, "王五");
                insertStat.setInt(2, 25);
                insertStat.executeUpdate();
                try (ResultSet idRs = insertStat.getGeneratedKeys()) {
                    while (idRs.next()) {
                        System.out.println(idRs.getObject(1));
                    }
                }
            }
        }
    }



    /**
     * 可滚动的结果集
     */
    @Test
    public void test7() throws SQLException {
        final Map<String, String> dbProperties = getDBProperties();
        try (Connection connection = DriverManager.getConnection(dbProperties.get("url"), dbProperties.get("user"), dbProperties.get("password"))) {
            String sql = "SELECT * FROM DESC WHERE ID = ?";
            try (PreparedStatement insertStat = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                insertStat.setString(1, "王五");
                insertStat.setInt(2, 25);
                insertStat.executeUpdate();
                try (ResultSet idRs = insertStat.getGeneratedKeys()) {
                    while (idRs.next()) {
                        System.out.println(idRs.getObject(1));
                    }
                }
            }
        }
    }

    private Map<String, String> getDBProperties() {
        Properties properties = new Properties();
        try (FileInputStream in = new FileInputStream("database.properties")) {
            properties.load(in);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        final HashMap map = new HashMap();
        map.put("url", url);
        map.put("user", user);
        map.put("password", password);
        return map;
    }
}
